package com.mdsi.teacher;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mdsi.models.Absence;
import com.mdsi.models.ConnectionManager;
import com.mdsi.models.Department;
import com.mdsi.models.Lesson;
import com.mdsi.models.Room;
import com.mdsi.models.Student;
import com.mdsi.models.Subject;
import com.mdsi.models.Teacher;

/**
 * Servlet implementation class LessonForSubject
 */
@WebServlet("/User/Teacher/LessonForSubject")
public class LessonForSubject extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LessonForSubject() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getParameter("subject") == null) {
			response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
			return ;
		}
		try {
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = conn.prepareStatement("SELECT DISTINCT ID_SUBJECT, NAME FROM SUBJECT WHERE ID_SUBJECT = ?") ;
			st.setLong(1, Long.valueOf(request.getParameter("subject"))) ;
			ResultSet result = st.executeQuery() ;
			if (!result.next()) {
				conn.close() ;
				response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
				return ;
			}
			getServletContext().setAttribute("subject", new Subject(result.getLong("ID_SUBJECT"), result.getString("NAME"))) ;
			st = conn
				.prepareStatement("SELECT DISTINCT L.SUBJECT.ID_SUBJECT, L.SUBJECT.NAME, "
						+ "L.ID_LESSON, L.START_HOUR, L.END_HOUR, "
						+ "L.ROOM.ID_ROOM, L.ROOM.NUM, L.ROOM.TYPE_ROOM, L.ROOM.DEPT.ID_DEPARTMENT, L.ROOM.DEPT.NAME "
						+ "FROM LESSON L "
						+ "WHERE L.TEACHER.EMAIL='" + ((Teacher)request.getSession().getAttribute("user")).getEmail() + "' "
						+ "AND L.SUBJECT.ID_SUBJECT = ?") ;
			st.setLong(1, Long.valueOf(request.getParameter("subject"))) ;
			result = st.executeQuery() ;
			ArrayList <Lesson> lessons = new ArrayList <Lesson> () ;
			while (result.next()) {
				Calendar start = GregorianCalendar.getInstance() ;
				start.setTime(result.getDate("START_HOUR")) ;
				Calendar end = GregorianCalendar.getInstance() ;
				end.setTime(result.getDate("END_HOUR")) ;
				Lesson lesson = new Lesson(
						result.getLong("ID_LESSON"),
						new Subject(result.getLong("SUBJECT.ID_SUBJECT"), 
								result.getString("SUBJECT.NAME")),
						new Room(result.getLong("ROOM.ID_ROOM"),
								Room.TypeRoom.valueOf(result.getString("ROOM.TYPE_ROOM")),
								new Department(result.getLong("ROOM.DEPT.ID_DEPARTMENT"), 
										result.getString("ROOM.DEPT.NAME")),
								result.getInt("ROOM.NUM")
								), 
						start, end, 
						(Teacher) request.getSession().getAttribute("user")
						) ;
				lessons.add(lesson) ;
				PreparedStatement stA = conn
						.prepareStatement("SELECT DISTINCT A.STUDENT.ID_STUDENT, A.STUDENT.FIRSTNAME, A.STUDENT.LASTNAME, A.STUDENT.EMAIL,  "
								+ "A.ID_ABSENCE, A.JUSTIFY, A.JUSTIFICATION, A.LEVELJUSTIFICATION, "
								+ "A.STUDENT.NUM, A.STUDENT.BIRTHDATE, A.STUDENT.NATIONALITY "
								+ "FROM LESSON L, ABSENCE A "
								+ "WHERE L.ID_LESSON = " + lesson.getId().toString() + " "
								+ "AND A.LESSON = REF(L)") ;
				ResultSet absences = stA.executeQuery();
				while (absences.next()) {
					Calendar birthdate = GregorianCalendar.getInstance() ;
					birthdate.setTime(absences.getDate("STUDENT.BIRTHDATE")) ;
					lesson.addAbsence(
							new Absence(absences.getLong("ID_ABSENCE"),
									lesson,
									new Student(absences.getLong("STUDENT.ID_STUDENT"), 
											absences.getString("STUDENT.FIRSTNAME"), 
											absences.getString("STUDENT.LASTNAME"),
											absences.getString("STUDENT.EMAIL"), 
											absences.getLong("STUDENT.NUM"), 
											birthdate, 
											absences.getString("STUDENT.NATIONALITY")),
									absences.getString("JUSTIFY").equals("Y"),
									absences.getString("JUSTIFICATION"),
									absences.getInt("LEVELJUSTIFICATION")
									)) ;
				}
			}
			conn.close () ;
			getServletContext().setAttribute("lessons", lessons) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		this.getServletContext().getRequestDispatcher("/WEB-INF/teacher/lesson_for_subject.jsp").forward(request, response);		
	}

}
